from dbutils.pooled_db import PooledDB
import pymysql
from base_class.base_utils import spend_time
from config import *
import json
from data_load_up import DataStep


class ReadFromMysql(object):
    def __init__(self, host=None, user=None,
                 password=None, port=None):
        self.host = host
        self.user = user
        self.password = password
        self.port = port
        self.pool = None

    @spend_time("创建连接池耗时： ")
    def makepool(self, database):
        """
        :param database: 数据库
        :return: 相应库连接
        """
        pool = PooledDB(pymysql, 3, host=self.host,
                        user=self.user,
                        passwd=self.password,
                        db=database,
                        port=self.port,
                        setsession=['SET AUTOCOMMIT = 1'])
        if self.pool == None:
            self.pool = pool
        else:
            self.pool.close()
            self.pool = pool

    @spend_time("获取连接耗时： ")
    def getconn(self):
        conn = self.pool.connection()  # 以后每次需要数据库连接就是用connection（）函数获取连接就好了
        return conn

    @spend_time("获取表头耗时： ")
    def column_from_mysql(self, table_name):
        """
        :param database_name: 库名
        :param table_name: 表名
        :return: 表列名
        """
        conn = self.getconn()
        cs1 = conn.cursor()
        count2 = cs1.execute('SHOW FULL FIELDS FROM %s' % table_name)
        result2 = list(cs1.fetchall())
        columns = [column_set[0] for column_set in result2]
        cs1.close()
        conn.commit()
        conn.close()
        # print("查询到%d个特征:" % count2)
        return columns

if __name__ == '__main__':
    mysql = ReadFromMysql(host='10.12.33.34',user="root",password="hm!123",port=3306)
    mysql.makepool(database)
    conn = mysql.getconn()
    sc = conn.cursor()
    column = mysql.column_from_mysql(tablename)
    sc.execute(f"select * from {tablename}")
    conn.commit()
    result = sc.fetchall()
    with open(tmp_path+init_filename, "a+",encoding='utf-8') as outFile:
        for i in result:
            a = dict(zip(column,i))
            b= json.dumps(a, ensure_ascii=False)
            outFile.write(b + ',\n')

    crmstep = DataStep(s3_path=crm_s3_path, url=crm_url, local_path=crm_local_path, buc=crm_bucket)
    crmstep.upload(crm_bucket, init_filename, tmp_path+init_filename)
